import numpy as np
import pandas as pd
from openpyxl import load_workbook

from common import load_sheet, column_names, merge_rows, comm_same_names, comm_same_forms



# 计算销售金额
def calc_sales(df):
    # select
    df = df[[column_names['comm_name'], column_names['comm_form'], 
             column_names['comm_quat'], column_names['cost'], 
             column_names['dpot_loss'], column_names['sale_amnt'], 
             column_names['chan_loss'], ]]
    # group
    df = df.groupby([column_names['comm_name'], column_names['comm_form']]).agg('sum').reset_index()
    # merge
    df = merge_rows(df, comm_same_names, comm_same_forms)
    # descending sort
    sorted_df = df.sort_values(column_names['comm_quat'], ascending=False)
    # add summary
    summary = pd.DataFrame({column_names['comm_name']: column_names['summary'], 
                            column_names['comm_form']: "", 
                            column_names['comm_quat']: df[column_names['comm_quat']].sum(), 
                            column_names['cost']:      df[column_names['cost']].sum(), 
                            column_names['dpot_loss']: df[column_names['dpot_loss']].sum(),
                            column_names['sale_amnt']: df[column_names['sale_amnt']].sum(),
                            column_names['chan_loss']: df[column_names['chan_loss']].sum(),
                            },
                            index=[len(df)])
    df = pd.concat([sorted_df, summary])
    # calc profit and profit rate
    df[column_names['profit']] = df[column_names['sale_amnt']] - df[column_names['chan_loss']] - \
        df[column_names['cost']] + df[column_names['dpot_loss']]
    df[column_names['profit_rate']] = df[column_names['profit']] / df[column_names['sale_amnt']] 
    df[column_names['profit_rate']] = df[column_names['profit_rate']].apply(lambda x: f'{x:.2%}')
    return df

def save_excel(xl_path, df, sheet_name):
    workbook = load_workbook(xl_path)
    # 
    if sheet_name in workbook:
        # Delete existing worksheet
        del workbook[sheet_name]
    # 
    worksheet = workbook.create_sheet(sheet_name)
    # 
    worksheet.append(list(df.columns))
    for row in df.values:
        worksheet.append(list(row))
    
    workbook.save(xl_path)

def sales_profit(wb_path, sheet_src_name, sheet_dest_name):

    # 加载sheet
    df = load_sheet(wb_path, sheet_src_name)
    # 
    df = calc_sales(df)
    # 保存至excel
    save_excel(wb_path, df, sheet_dest_name)



if __name__ == '__main__':

    # 
    wb_path = 'data/01-02销售日报.xlsx'
    sheet_src_name = '订单明细'
    sheet_dest_name = '商品利润率'
    sales_profit(wb_path, sheet_src_name, sheet_dest_name)
